﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DBHelper
/// </summary>
public class DBHelper
{
	    #region Properties

        public SqlDataReader data { get; set; }
        public DataTable dataTable { get; set; }
        private string connectionString { get; set; }
        private SqlConnection _connection { get; set; }
        private SqlCommand _cmd { get; set; }

        #endregion

        // Local
        public DBHelper(string dataSource, string database)
        {
            this.connectionString = @"Data Source=" + dataSource + "; Initial Catalog=" + database + "; Integrated Security=True";
        }

        // External with usr and pwd
        public DBHelper(string dataSource, string user, string password)
        {
            this.connectionString = @"Data Source=" + dataSource + ";Persist Security Info=True;User ID=" + user + ";Password=" + password;
        }

        // Connect to database
        public bool Connect()
        {
            try
            {
                _connection = new SqlConnection(this.connectionString);
                _cmd = new SqlCommand();
                _cmd.Connection = _connection;
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("En forbindelse til databasen, kunne ikke oprettes");
                throw e;
            }
        }

        // Åben forbindelse
        public void Open() { _connection.Open(); } //con error when no con to db

        // Luk forbindelse
        public void Close() { _connection.Close(); }

        // Indsæt ny row
        public bool InsertRow(string table, string fields, string values) { return HandleRow("INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")"); }

        // Update data
        public bool UpdateRow(string table, string fieldsAndValues, string where) { return HandleRow("UPDATE " + table + " SET " + fieldsAndValues + " WHERE " + where); }

        // Delete row
        public bool DeleteRow(string table, string where) { return HandleRow("DELETE FROM " + table + " WHERE " + where); }

        private bool HandleRow(string query)
        {
            _cmd.CommandText = query; // Sæt SQL
            int affectedRows = _cmd.ExecuteNonQuery(); // Udfør CMD
            return (affectedRows == 1) ? true : false; // Retuner true eller false   
        }

        // Hent data
        public void SelectData(string fields, string table, string where, string orderBy)
        {
            string sql;
            string what = (fields == string.Empty) ? "*" : fields;

            sql = "SELECT " + what + " ";
            sql += "FROM " + table + " ";
            if (where != "")
                sql += "WHERE " + where + " ";
            if (orderBy != "")
                sql += "ORDER BY " + orderBy;

            // Sæt SQL
            _cmd.CommandText = sql;

            //sæt cmd type
            _cmd.CommandType = CommandType.Text;

            // Udfør CMD
            this.data = _cmd.ExecuteReader();
        }

        // Hent data - Stored Procedure
        public void SelectDataSP(string StoredProcedureName, params object[] pars)
        {
            // Sæt SQL
            _cmd.CommandText = StoredProcedureName;
            _cmd.Parameters.Clear();
            // Sæt CMD type
            _cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < pars.Count(); i += 2)
            {
                SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
                _cmd.Parameters.Add(par);
            }
            // Clear parametre

            // Udfør CMD
            this.data = _cmd.ExecuteReader();
        }

        public DataTable ExcuteQuery(string sql, CommandType commandType, params object[] pars)
        {
            _cmd.CommandType = commandType;

            for (int i = 0; i < pars.Count(); i += 2)
            {
                SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
                _cmd.Parameters.Add(par);
            }
            SqlDataAdapter Adap = new SqlDataAdapter(_cmd);
            DataSet dst = new DataSet();
            Adap.Fill(dst);
            return dst.Tables[0];
        }

        public bool ExcuteNonQuery(string sql, CommandType commandtype, params object[] pars)
        {
            int NoOfRowEffect = 0;
            _cmd.CommandType = commandtype;

            for (int i = 0; i < pars.Count(); i += 2)
            {
                SqlParameter par = new SqlParameter(pars[i].ToString(), pars[i + 1]);
                _cmd.Parameters.Add(par);
            }
           NoOfRowEffect = _cmd.ExecuteNonQuery();
           return NoOfRowEffect != 0;
        }
}
